CREATE PROCEDURE [dbo].[sp_asi_UpdateFundraisingViewTables]
    @RefreshAll bit = 0
AS
BEGIN
     IF (@RefreshAll = 1)
     BEGIN
        DELETE TransWatch
        DELETE GiftReport
        DELETE DonationReport
        DELETE PledgeReport

        INSERT TransWatch(TransactionNumber, InvoiceNumber, CaptureDate)
        SELECT DISTINCT TRANS_NUMBER, INVOICE_REFERENCE_NUM, getDate()
        FROM Trans
     END

     SELECT TransactionNumber, InvoiceNumber, CaptureDate
     INTO #TransTemp
     FROM TransWatch

     DELETE GiftReport
     FROM GiftReport
        INNER JOIN #TransTemp
            ON GiftReport.TransactionNumber = #TransTemp.TransactionNumber
            AND GiftReport.InvoiceReferenceNumber = #TransTemp.InvoiceNumber

     DELETE GiftReport
     FROM GiftReport
        INNER JOIN #TransTemp
            ON GiftReport.OriginalTransaction = #TransTemp.TransactionNumber
     WHERE
            GiftReport.SourceSystem = 'MEETING'
     
     INSERT INTO GiftReport(ID, OriginalTransaction, TransactionNumber, SourceSystem, TransactionDate, DateReceived, Amount, FairMktValue, Fund, AppealCode, SolicitorID, CheckNumber, PaymentType, CampaignCode, FiscalYear, FiscalMonth, GiftType, InvoiceReferenceNumber, ReceiptID, MatchingTransaction, IsMatchingGift, MemorialID, PledgeID, ListAs, RequestNumber, InstallmentDate, MemorialNameText)
    (
     SELECT ID, OriginalTransaction, vGift.TransactionNumber, SourceSystem, TransactionDate, DateReceived, Amount, vGift.FairMktValue, Fund, Appeal, SolicitorID, CheckNumber, PaymentType, Campaign, FiscalYear, FiscalMonth, GiftType, InvoiceReferenceNumber, ReceiptID, MatchingTransaction, IsMatchingGift, MemorialID, PledgeID, ListAs, RequestNumber, InstallmentDate, MemorialNameText FROM vGift
        INNER JOIN #TransTemp
            ON vGift.TransactionNumber = #TransTemp.TransactionNumber
            AND vGift.InvoiceReferenceNumber = #TransTemp.InvoiceNumber
     UNION
     SELECT ID, OriginalTransaction, vGift.TransactionNumber, SourceSystem, TransactionDate, DateReceived, Amount, vGift.FairMktValue, Fund, Appeal, SolicitorID, CheckNumber, PaymentType, Campaign, FiscalYear, FiscalMonth, GiftType, InvoiceReferenceNumber, ReceiptID, MatchingTransaction, IsMatchingGift, MemorialID, PledgeID, ListAs, RequestNumber, InstallmentDate, MemorialNameText FROM vGift
        INNER JOIN #TransTemp
            ON vGift.OriginalTransaction = #TransTemp.TransactionNumber
     WHERE
            vGift.SourceSystem = 'MEETING'
            
    )

     UPDATE GiftReport SET MemorialTributeType = Activity.Mem_trib_code,MemorialTributeMessage=ISNULL(Activity.NOTE_2,''), TributeNotificationContactID = Trans_Notify.TRANS_NUMBER
     FROM Activity LEFT OUTER JOIN Trans_Notify ON Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM LEFT OUTER JOIN GiftReport ON GiftReport.OriginalTransaction = Activity.ORIGINATING_TRANS_NUM
     WHERE EXISTS (SELECT Trans_Notify.TRANS_NUMBER FROM Trans_Notify WHERE GiftReport.OriginalTransaction  IS NOT NULL AND Trans_Notify.TRANS_NUMBER = GiftReport.OriginalTransaction )


     DELETE DonationReport
     FROM DonationReport
     WHERE OriginalTransaction IN (SELECT TransWatch.TransactionNumber FROM TransWatch )

     INSERT INTO DonationReport(ID, OriginalTransaction, InvoiceRefNum, SourceSystem, TransactionDate, DateReceived, Amount, SolicitorID, CheckNumber, PaymentType, FiscalMonth, FiscalYear, GiftType, MatchingTransaction, IsMatchingGift, MemorialID, ListAs, RequestNumber, MemorialNameText)
     SELECT ID, OriginalTransaction, InvoiceRefNum, SourceSystem, TransactionDate, DateReceived, Amount, SolicitorID, CheckNumber, PaymentType, FiscalMonth, FiscalYear, GiftType, MatchingTransaction, IsMatchingGift, MemorialID, ListAs, RequestNumber, MemorialNameText FROM vDonations
     WHERE OriginalTransaction IN (SELECT TransWatch.TransactionNumber FROM TransWatch )

     DELETE PledgeReport
     FROM PledgeReport
        INNER JOIN (SELECT DISTINCT InvoiceNumber FROM #TransTemp) invoices
            ON PledgeReport.InvoiceReferenceNumber = invoices.InvoiceNumber

     INSERT INTO PledgeReport(ID, TransactionType, TransactionNumber, InvoiceReferenceNumber, TransactionDate, DateReceived, FiscalYear, FiscalMonth, AppealCode, CampaignCode, Fund, SolicitorID, SourceSystem, MatchingTransaction, IsMatchingGift, MemorialID, ListAs, RequestNumber, InstallmentDate, LastPaymentDate, PledgeAmount, AdjustmentsAmount, PaymentsAmount, PledgeFairMarketValue, MemorialNameText)
     SELECT ID, TransactionType, vPledgeData.TransactionNumber, InvoiceReferenceNumber, TransactionDate, DateReceived, FiscalYear, FiscalMonth, Appeal, Campaign, Fund, SolicitorID, SourceSystem, MatchingTransaction, IsMatchingGift, MemorialID, ListAs, RequestNumber, InstallmentDate, LastPaymentDate, PledgeAmount, AdjustmentsAmount, PaymentsAmount, PledgeFairMarketValue, MemorialNameText FROM vPledgeData
        INNER JOIN (SELECT DISTINCT InvoiceNumber FROM #TransTemp) invoices
            ON vPledgeData.InvoiceReferenceNumber = invoices.InvoiceNumber

     DELETE TransWatch
     FROM TransWatch
        INNER JOIN #TransTemp
            ON TransWatch.TransactionNumber = #TransTemp.TransactionNumber AND
                TransWatch.InvoiceNumber = #TransTemp.InvoiceNumber AND
                TransWatch.CaptureDate = #TransTemp.CaptureDate
     DROP TABLE #TransTemp
END



GO
GRANT EXECUTE ON  [dbo].[sp_asi_UpdateFundraisingViewTables] TO [IMIS]
GO